-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: insert values duplicate key update trigger相关功能验证
-- @history: Modified by peilinqian at 2025-1-20；报错信息非本用例测试点，规避upsert_tri c4,c5字段的影响
--pre1:切换B库执行
@conn PrimaryDbBmode;

--step1:创建表，创建function;expect:成功
drop table if exists upsert_base,upsert_tri;
create table upsert_base
(c1 serial primary key,
c2 varchar(100),
cold varchar(1024),
cnew varchar(1024))
with (storage_type=ustore);

create table upsert_tri
(c1 serial,
c2 bigint default floor(random()*1000),
c3 varchar(1023) default repeat(random(),10),
unique(c1,c3))
with (storage_type=ustore);

create or replace function trig_1() returns trigger as $emp_audit$
begin
if (tg_op = 'INSERT') then
insert into upsert_base(c2,cnew) values('insert',new.c2);
return new;
elsif (tg_op = 'UPDATE') then
insert into upsert_base(c2,cold,cnew) values('update',old.c2,new.c2);
return new;
end if;
return null;
end;
$emp_audit$ language plpgsql;
/

--step2:在触发事件发生后执行trigger;expect:成功
create trigger tri_a1
after update or insert on upsert_tri
for each row execute procedure trig_1(); 
/

insert into upsert_tri(c1,c2,c3) values(1000,1,1) on duplicate key update nothing;
insert into upsert_tri(c1,c2,c3) values(1000,2,1) on duplicate key update nothing;
select c2,cold,cnew from upsert_base order by 1;
select c1,c2,c3 from upsert_tri order by 1;
insert into upsert_tri(c1,c2,c3) values(1000,3,1) on duplicate key update c2=c2+1;
insert into upsert_tri(c1,c2,c3) values(2000,4,1) on duplicate key update c2=c2+1;
select c2,cold,cnew from upsert_base order by 1;
select c1,c2,c3 from upsert_tri order by 1;

--step3:在触发事件发生前执行trigger;expect:成功
drop trigger tri_a1 on upsert_tri;
truncate upsert_base,upsert_tri;
create trigger tri_b1
before update or insert on upsert_tri
for each row execute procedure trig_1();
/
insert into upsert_tri(c1,c2,c3) values(1000,1,1) on duplicate key update nothing;
insert into upsert_tri(c1,c2,c3) values(1000,2,1) on duplicate key update nothing;
select c2,cold,cnew from upsert_base order by 1;
select c1,c2,c3 from upsert_tri order by 1;
insert into upsert_tri(c1,c2,c3) values(1000,3,1) on duplicate key update c2=c2+1;
insert into upsert_tri(c1,c2,c3) values(2000,4,1) on duplicate key update c2=c2+1;
select c2,cold,cnew from upsert_base order by 1;
select c1,c2,c3 from upsert_tri order by 1;

--step4:环境清理;expect:成功
drop trigger tri_b1 on upsert_tri;
drop table upsert_base;
drop table upsert_tri;
drop function trig_1();